package com.iengoo.day08.dto;

import com.iengoo.day08.entity.Ticket;

import java.sql.*;

/**
 * @author zpym
 * @CopyRight (C) http://www.iengoo.com
 * @brief
 * @email 122162005@qq.com
 * @date 2019-01-18 14:51
 */
public class TicketDao {
    private String url = "jdbc:mysql://192.168.95.109:3306/station?useUnicode=true&characterEncoding=UTF-8&userSSL=false&serverTimezone=GMT%2B8";
    private String user = "root";
    private String password = "liwei";
    /**
     * 增加车票
     *
     * @param ticket
     */
    public boolean add(Ticket ticket) {
        try {
            Class.forName("com.mysql.cj.jdbc.Driver");
            Connection conn = DriverManager.getConnection(url, user, password);
            String sql = "INSERT INTO ticket (`start_time`,`start_station`," +
                    "`stop_station`,`price`,`surplus_ticket`,`type`) " +
                    "VALUES (?,?,?,?,?,?)";
            PreparedStatement statement = conn.prepareStatement(sql);
            // 将第 1 个占位符设置成 ticket.getStartTime()
            statement.setTime(1, ticket.getStartTime());
            statement.setString(2, ticket.getStartStation());
            statement.setString(3, ticket.getStopStation());
            statement.setInt(4, ticket.getPrice());
            statement.setInt(5, ticket.getSurplusTicket());
            statement.setString(6, ticket.getType());

            int i = statement.executeUpdate();

            statement.close();
            conn.close();

            if (i > 0) {
                System.out.println("创建车信息成功");
                return true;
            } else {
                System.out.println("创建车信息失败");
                return false;
            }

        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (SQLException e) {
            e.printStackTrace();
        }

        return false;
    }

    /**
     * 根据 id删除车票信息
     *
     * @param id
     */
    public void delete(int id) {
        // todo
    }

    /**
     * @param ticket
     */
    public void update(Ticket ticket) {
        /*
         * 1. 判断ticket对象的ID是否存在
         * 2. ID存在，再更新，否则，直接结束方法
         */

        if (ticket.getId() == 0) {
            return;
        }

        try {
            Class.forName("com.mysql.cj.jdbc.Driver");
            Connection conn = DriverManager.getConnection(url, user, password);
            String sql = "UPDATE ticket SET `start_time`=?,`start_station`=?," +
                    "`stop_station`=?,`price`=?,`surplus_ticket`=?,`type`=? WHERE id = ?";
            PreparedStatement statement = conn.prepareStatement(sql);

            statement.setTime(1, ticket.getStartTime());
            statement.setString(2, ticket.getStartStation());
            statement.setString(3, ticket.getStopStation());
            statement.setInt(4, ticket.getPrice());
            statement.setInt(5, ticket.getSurplusTicket());
            statement.setString(6, ticket.getType());
            statement.setInt(7, ticket.getId());
            statement.executeUpdate();

            statement.close();
            conn.close();
        } catch (Exception e) {
            e.printStackTrace();
        }

    }

    public Ticket find(String startStation, String stopStation) {
        // todo
        return null;
    }

    /**
     *
     * @param id
     * @return
     */
    public Ticket findById(int id) {
        try {
            Class.forName("com.mysql.cj.jdbc.Driver");
            Connection conn = DriverManager.getConnection(url, user, password);
            String sql = "SELECT id, `start_time`,`start_station`,`stop_station`,`price`,`surplus_ticket`,`type` FROM ticket WHERE id = ?";
            PreparedStatement statement = conn.prepareStatement(sql);

            statement.setInt(1, id);
            ResultSet rs = statement.executeQuery();
            // 创建一个 ticket 对象存储结果集中的数据
            Ticket t = new Ticket();
            if (rs.next()) {
                t.setId( rs.getInt("id") );
                t.setStartTime( rs.getTime("start_time") );
                t.setStartStation(rs.getString("start_station"));
                t.setStopStation(rs.getString("stop_station"));
                t.setPrice(rs.getInt("price"));
                t.setSurplusTicket(rs.getInt("surplus_ticket"));
                t.setType(rs.getString("type"));
            }


            statement.close();
            conn.close();

            return t;
        } catch (Exception e) {
            e.printStackTrace();
        }

        return null;

    }
}
